// repositories/score.go
package repositories

import (
	"fmt"
	"myapi/models"
)

// ScoreRepository 处理成绩数据库操作
type ScoreRepository struct{}

// Create 创建新成绩
func (r *ScoreRepository) Create(score *models.Score) error {
	return DB.Create(score).Error
}

// 根据用户id和科目id查看是否已经有成绩了
func (r *ScoreRepository) FindByUserIDAndSubjectID(userID, subjectID uint) (*models.Score, error) {
	var score models.Score
	err := DB.Where("user_id = ? AND subject_id = ?", userID, subjectID).First(&score).Error
	if err != nil {
		return nil, err
	}
	return &score, nil
}

// FindByID 通过 ID 查找成绩
func (r *ScoreRepository) FindByID(id uint) (*models.Score, error) {
	var score models.Score
	err := DB.Preload("User").Preload("Subject").First(&score, id).Error
	if err != nil {
		return nil, err
	}
	return &score, nil
}

// FindByUserID 查找用户的所有成绩
func (r *ScoreRepository) FindByUserID(userID uint, page, pageSize int) ([]models.Score, int64, error) {
	var scores []models.Score
	var count int64

	offset := (page - 1) * pageSize

	// 获取总数
	if err := DB.Model(&models.Score{}).Where("user_id = ?", userID).Count(&count).Error; err != nil {
		return nil, 0, err
	}

	// 获取分页数据
	if err := DB.Where("user_id = ?", userID).Preload("User").Preload("Subject").
		Offset(offset).Limit(pageSize).Find(&scores).Error; err != nil {
		return nil, 0, err
	}

	return scores, count, nil
}

// FindBySubjectID 查找科目的所有成绩
func (r *ScoreRepository) FindBySubjectID(subjectID uint, page, pageSize int) ([]models.Score, int64, error) {
	var scores []models.Score
	var count int64

	offset := (page - 1) * pageSize

	// 获取总数
	if err := DB.Model(&models.Score{}).Where("subject_id = ?", subjectID).Count(&count).Error; err != nil {
		return nil, 0, err
	}

	// 获取分页数据
	if err := DB.Where("subject_id = ?", subjectID).Preload("User").Preload("Subject").
		Offset(offset).Limit(pageSize).Find(&scores).Error; err != nil {
		return nil, 0, err
	}

	return scores, count, nil
}

// FindAll 获取所有成绩
func (r *ScoreRepository) FindAll(page, pageSize int) ([]models.Score, int64, error) {
	var scores []models.Score
	var count int64

	offset := (page - 1) * pageSize

	// 获取总数
	if err := DB.Model(&models.Score{}).Count(&count).Error; err != nil {
		return nil, 0, err
	}

	// 获取分页数据
	if err := DB.Preload("User").Preload("Subject").
		Offset(offset).Limit(pageSize).Find(&scores).Error; err != nil {
		return nil, 0, err
	}

	return scores, count, nil
}

// Update 更新成绩信息
func (r *ScoreRepository) Update(score *models.Score) error {
	return DB.Save(score).Error
}

// Delete 删除成绩
func (r *ScoreRepository) Delete(id uint) error {
	return DB.Delete(&models.Score{}, id).Error
}

// 查找单科成绩前三的三位同学
func (r *ScoreRepository) FindTopThreeScores(subjectID uint) ([]models.Score, error) {
	var scores []models.Score
	err := DB.Where("subject_id = ?", subjectID).Preload("User").Preload("Subject").Order("score DESC").Limit(3).Find(&scores).Error
	if err != nil {
		return nil, err
	}
	fmt.Println(scores, err, subjectID)
	return scores, nil
}

// 查找单科目最低分
func (r *ScoreRepository) FindLowestScore(subjectID uint) (*models.Score, error) {
	var score models.Score
	err := DB.Table("scores").
		Select("scores.*, users.name as user_name, subjects.name as subject_name").
		Joins("JOIN users ON scores.user_id = users.id").
		Joins("JOIN subjects ON scores.subject_id = subjects.id").
		Where("scores.subject_id = ?", subjectID).
		Order("scores.score ASC").
		Limit(1).
		Scan(&score).Error
	if err != nil {
		return nil, err
	}
	return &score, nil
}
